Microsoft Learning Components
LearningStore

LearningStore is the database access component of Microsoft Learning Components. MLC and applications that use MLC use the LearningStore API to read from and write to the database. This section explains how to use LearningStore.

As prerequisites to this section, please read the following sections. (Note that some parts of these sections are specific to SharePoint Learning Kit (SLK). If you are not using SLK and instead are developing an application that leverages MLC, you can think of SLK as just one example of how to build such an application.)

A LearningStore Walk-Through

LearningStore uses Microsoft SQL Server to store information in tables—if you're familiar with databases, this concept won't be new to you. For example, the MLC base schema includes a table of users. The following is what you might see if you use Microsoft SQL Server Management Studio to open the UserItem table of an MLC database (for example, a SharePoint Learning Kit database).

Id Key Name AudioCaptioning AudioLevel DeliverySpeed Language
1 S-1-5-21-...-1048 Fabien Hernoux 0 1 1
2 S-1-5-21-...-1044 Ellen Adams 0 1 1
4 S-1-5-21-...-1026 Allan Vinther-Wahl 0 1 1
5 S-1-5-21-...-1028 Andreas Herbinger 0 1 1
6 S-1-5-21-...-1036 Chris Ashton 0 1 1
7 S-1-5-21-...-1042 Jeff Hay 0 1 1
8 S-1-5-21-...-1030 Anne Grethe Hansen 0 1 1
9 S-1-5-21-...-1032 Arno Harteveld 0 1 1
10 S-1-5-21-...-1050 Giorgio Veronesi 0 1 1
11 S-1-5-21-...-1034 Bernhard Heger 0 1 1
12 S-1-5-21-...-1038 Claus Hansen 0 1 1

This is a table named "UserItem", containing 12 rows and 7 columns. LearningStore uses different terminology than SQL Server, as follows:

SQL Server Concept LearningStore Concept
Table Item Type
Row Item
Column Property

So, using LearningStore terminology, the preceding UserItem table represents a collection of 12 items that each have an item type of "UserItem". Each item has an implicit "Id" property (of type "ItemIdentifier") plus six explicitly-defined properties. The properties are named the same as the columns: "Key", "Name", "AudioCaptioning", and so on. UserItem is defined in the MLC base schema using the following XML:

XML
<ItemType Name="UserItem">
<Properties>
<Property Name="Key" Type="String" Size="250" />
<Property Name="Name" Type="String" Size="255" />
<Property Name="AudioCaptioning" Type="Enum" EnumName="AudioCaptioning">
<Default>0</Default>
</Property>
<Property Name="AudioLevel" Type="Single">
<Default>1</Default>
</Property>
<Property Name="DeliverySpeed" Type="Single">
<Default>1</Default>
</Property>
<Property Name="Language" Type="String" Size="255">
<Default />
</Property>
</Properties>
<Indexes>
<Index>
CREATE INDEX KeyIndex
ON UserItem([Key])
</Index>
</Indexes>
</ItemType>

The details of this XML format are described later in this section, but you can see that this XML specifies the name of the item type ("UserItem") and the names and types of each property.

LearningStore supports the following property types:

Name In Schema XML .NET CLR Type SQL Type
Type="Boolean" Boolean bit
Type="ByteArray" Byte array varbinary
Type="DateTime" DateTime datetime
Type="Double" Double float(53)
Type="Enum" Enum int
Type="Guid" Guid uniqueidentifier
Type="ItemIdentifier" LearningStoreItemIdentifier bigint
Type="Int32" Int32 int
Type="Single" Single float(24)
Type="String" String nvarchar
Type="Xml" LearningStoreXml xml

Note that, in the XML above, the property "AudioCaptioning" is defined as Type="Enum" EnumName="AudioCaptioning". This means that AudioCaptioning is an enumeration. MLC's base schema defines this enumeration using the following XML:

XML
<Enum Name="AudioCaptioning">
<Values>
<Value Name="Off" Value="-1" />
<Value Name="NoChange" Value="0" />
<Value Name="On" Value="1" />
</Values>
</Enum>

To help applications work with LearningStore item types, MLC contains class and enumeration definitions that are generated by the LearningStore schema compiler. In the case of the UserItem example, the following types are automatically generated (among others):

C#
// in Microsoft.LearningComponents.Storage.dll:
namespace Microsoft.LearningComponents.Storage.BaseSchema
{
    public abstract class UserItem
    {
        public const string ItemTypeName = "UserItem";
        public const string Id = "Id";
        public const string Key = "Key";
        public const string Name = "Name";
        public const string AudioCaptioning = "AudioCaptioning";
        public const string AudioLevel = "AudioLevel";
        public const string DeliverySpeed = "DeliverySpeed";
        public const string Language = "Language";
        public const int MaxKeyLength = 250;
        public const int MaxNameLength = 255;
        public const int MaxLanguageLength = 255;
    }
}

// in Microsoft.LearningComponents.Storage.dll:
namespace Microsoft.LearningComponents.Storage
{
    public class UserItemIdentifier : LearningStoreItemIdentifier
    {
        public UserItemIdentifier(LearningStoreItemIdentifier id);
        public UserItemIdentifier(long key);
    }
}

// in Microsoft.LearningComponents.dll:
namespace Microsoft.LearningComponents
{
    public enum AudioCaptioning
    {
        Off = -1,
        NoChange = 0,
        On = 1
    }
}

The UserItem class (above) defines constants that help an application perform queries. This class may seem odd at first: why have a constant named "AudioCaptioning" with a value of "AudioCaptioning"? The answer may become clear in the following sample code. (This sample displays the contents of the "Name" column of the UserItem table, sorted alphabetically. If you have SharePoint Learning Kit installed, you can try this sample code in Visual Studio 2005: just create a new C# console application project, add references to Microsoft.LearningComponents.dll and Microsoft.LearningComponents.Storage.dll (available in Debug or Release directory of "SLK-SDK-n.n.nnn-ENU.zip"), and paste in the code below. You'll need to adjust the connection string if you're not using server "localhost" and database name "SharePointLearningKit".)

C#
using System;
using System.Data;
using Microsoft.LearningComponents.Storage;
using Schema = Microsoft.LearningComponents.Storage.BaseSchema;

class Program
{
    static void Main(string[] args)
    {
        LearningStore learningStore = new LearningStore(
            "Server=localhost;Database=SharePointLearningKit;Integrated Security=true",
            String.Empty, true);
        LearningStoreJob job = learningStore.CreateJob();
        LearningStoreQuery query = learningStore.CreateQuery(Schema.UserItem.ItemTypeName);
        query.AddColumn(Schema.UserItem.Name);
        query.AddSort(Schema.UserItem.Name, LearningStoreSortDirection.Ascending);
        job.PerformQuery(query);
        DataRowCollection dataRows = job.Execute<DataTable>().Rows;
        foreach (DataRow dataRow in dataRows)
        {
            Console.WriteLine(dataRow[Schema.UserItem.Name]);
        }
    }
}

The example above reference the MLC LearningStore schema, not the SLK schema, but then proceeds to access a SLK database. This works because this example is using an item type (UserItem) which is common to both MLC and SLK. If you want to be able to access SLK-specific item types (e.g. AssignmentItem), views (e.g. LearnerAssignmentListForInstructors), or SLK extensions to MLC item types (e.g. PackageItem.Warnings), you would need to add a reference to Microsoft.SharePointLearningKit.dll and change the using Schema line above to the following:

C#
using Schema = Microsoft.SharePointLearningKit.Schema;

This sample application shows that LearningStore uses string names when referring to item types—so the names are provided as constants to reduce the likelihood of coding errors. This application also demonstrates the basic steps required to perform a query in LearningStore:

  1. Create an instance of the LearningStore class. At a minimum, the constructor requires a standard database connection string as well as a string that identifies the current user for authorization purposes. In this case, String.Empty is used for the current user, and an extra flag is added that tells LearningStore to skip authorization. (This is typically done for trusted applications that perform adminstrative functions.)
  2. Call LearningStore.CreateJob to create an instance of the LearningStoreJob class. A LearningStoreJob represents a "batch" of work that needs to be performed in the database. In this example the job contains only one operation (a query), but in general jobs can contain any number of queries, add-item operations, add-or-update item operations, update-item operations, delete-item operations, and security right demands (explained later). Batching multiple operations into a single job improves performance.
  3. Call LearningStore.CreateQuery to create an instance of the LearningStoreQuery class, passing an argument that indicates the LearningStore item type or view to query. (Views are discussed below.)
  4. Call LearningStoreQuery.AddColumn once for each column you'd like to return in the query results. LearningStore query results take the form of a standard DataTable.
  5. Not shown: Call LearningStoreQuery.AddCondition once for each condition you'd like to add to the query. A condition takes the form "property operator value" (though not specified as a string); for example, conceptually, "Id = 7". If you add multiple conditions, they're "AND-ed" together, i.e. an item (row) is returned only if it matches all consitions.
  6. Optionally, call LearningStoreQuery.AddSort one or more times to specify columns to sort on.
  7. Call LearningStoreJob.PerformQuery to add the query to the job. This doesn't execute the job—it just adds the query to the "batch".
  8. Call LearningJob.Execute to execute the job.
  9. Retrieve the query results by enumerating through the rows of the returned DataTable.

Of course, this sample application doesn't demonstrate the power of LearningStore—in fact this particular job could be done just as easily using ADO.NET. But it should give you the basic idea of how LearningStore works. The following sections explain LearningStore concepts in more detail.

LearningStore Concepts

The following are a few key LearningStore concepts:

For more reference information, see LearningStore class documentation. For example of how to code using LearningStore, see:

As an alternative to using LearningStore to read an MLC-maintained database, you can read the database directly. (LearningStore does not place an "abstraction barrier" in front of the database.) However, keep the following in mind:

Security in LearningStore

A LearningStore schema includes security rules that determine if a given user is allowed to see a particular piece of information or perform a given operation. For example, in SharePoint Learning Kit, learners can only view or submit assignments assigned to them.

The application can choose to turn on or off security checking. When it's turned on, LearningStore queries will only return information the current user is entitled to see, and will throw an exception if the application attempts to perform an unauthorized operation. (Applications can turn off security in order to perform administrative functions that aren't in the context of a given user.) For most Web applications, the "current user" is the browser user.

MLC's base schema is "secure by default": if not overriden by the application's schema, no operations are valid and no information may be returned. That leaves an application with two choices: either leave LearningStore security turned off, or define security rules.

For more information about defining security rules, see:

Base vs. Application Schema

A key motivation behind the design of LearningStore is the recognition that, in order to operate efficiently, the database schema of MLC needs to be merged (at least partly) with an the database schema of an application. This can be illustrated by two examples of applications that leverage MLC:

  1. SharePoint Learning Kit (SLK) defines the concepts of assignment (an arbitrary number of instructors and learners associated with a single e-learning package) and learner assignment (data related to one learner's interaction with a given assignment). SLK's schema defines a LearnerAssignmentItem table which contains one row per learner assignment; this table contains a few pieces of information (e.g. instructor comments and override score) not included in MLC's base schema. When a learner begins an assignment, an MLC attempt is created, and is associated with that LearnerAssignmentItem row. Since SLK's schema is implemented using LearningStore, SLK can perform efficient queries that join the SLK LearnerAssignmentItem table with the MLC AttemptItem table. In addition to performance benefits, an application using the SLK API sees a single, coherent SLK schema that incorporates the entire MLC schema, rather than an two distinct data stores—and SLK didn't need to abstract the entire MLC schema to accomplish this goal.
  2. BasicWebPlayer (BWP), a sample Web application included in the SLK/MLC SDK, doesn't have the concepts of "assignment" or "instructor". Instead, in BWP's world, packages are entirely self-assigned, and each package is "owned" by a specific learner.

It should be clear by now that concepts like "assignment" and "instructor" are not built-in to MLC. A goal of MLC is to define only the minimally-required e-learning constructs, so that applications are free to define these higher-level concepts in whatever manner works best for them.

One point of possible confusion: in the preceding paragraphs, the word "application" is used in two ways:

  1. In some cases, "application" is used to refer to a program that has extended the MLC schema. Under this definition, SLK and BWP are "applications".
  2. In other cases, "application" is used to refer to a program that accesses the "API" of a program that has extended the MLC schema. For example, a console application that uses the SLK API, or that uses the LearningStore API to access a SLK databae, falls under this definition.

Both these scenarios are supported, but keep in mind an important restriction: only one level of "extension" is supported by LearningStore. Application A may extend MLC's base schema, but application B may not extend Application A's schema. Consider this example: SLK includes a LearningStore schema file, SlkSchema.xml, that defines additions to the MLC base schema. It is not possible to create an application, say MyApp, that defines schema file MyAppSchema.xml, that extends SlkSchema.xml, which in turn extends the MLC base schema. Only one level of "extension" is allowed. (However, in the case of SLK, there are various options available for editing SlkSchema.xml.)

Running the LearningStore Schema Compiler

The LearningStore schema compiler (i.e. the SchemaCompiler.exe console application that's included in the SLK/MLC SDK) converts LearningStore schema XML into several different forms:

Other command-line options supported by SchemaCompiler.exe:

SchemaCompiler.exe accepts the name of the schema XML as a command-line parameter, followed by one or more options listed above. For example, the following is the command line used to compile the SharePoint Learning Kit schema:

Command Line
SchemaCompiler.exe SlkSchema.xml /OutputInit SlkSchema.sql /OutputUpgrade SlkUpgrade.sql /OutputHelper SlkSchema.cs /SchemaNamespace Microsoft.SharePointLearningKit.Schema /Namespace Microsoft.SharePointLearningKit

SlkSchema.xml is the input file; the other files are outputs. To initialize a database with this schema, execute SlkSchema.sql using a tool such as sqlcmd.exe. (Don't forget to run iisreset.exe after upgrading the schema if you're application is based on ASP.NET.) To upgrade a database (assuming only views and security rules have changed), use SlkUpgrade.sql instead of SlkSchema.sql.

If a subset of output files is needed, you can specify a subset of command-line options. For example, the following command line generates only SqlUpgrade.sql, not SqlSchema.sql.

Command Line
SchemaCompiler.exe SlkSchema.xml /OutputUpgrade SlkUpgrade.sql /SchemaNamespace Microsoft.SharePointLearningKit.Schema /Namespace Microsoft.SharePointLearningKit
For More Information
Microsoft.LearningComponents Namespace (API Reference)
Microsoft.LearningComponents.DataModel Namespace (API Reference)
Microsoft.LearningComponents.Storage Namespace (API Reference)
SLK Queries and Query Sets
SLK+MLC Schema Overview
SLK+MLC Schema Reference
LearningStore Schema Reference
Next Topic: Package Stores